package com.xmy.cultivate.mapper;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.baomidou.mybatisplus.extension.handlers.JacksonTypeHandler;
import com.xmy.cultivate.entity.Student;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xmy.cultivate.entity.views.GradeStudent;
import com.xmy.cultivate.entity.views.SelectStudent;
import org.apache.ibatis.annotations.*;

import java.math.BigDecimal;
import java.util.List;

/**
 * <p>
 * 学生表 Mapper 接口
 * </p>
 *
 * @author min
 * @since 2022-07-26
 */
@Mapper
public interface StudentMapper extends BaseMapper<Student> {

    @Select("select * from student where id=#{id}")
    public abstract Student findOne(@Param("id") Long id);

    @Select("select id,CONCAT(name,remark) as name,school_id,head_img,sex,grade_id,class_name,school_name,address,is_guanzhu,is_gather,status,linkman,linkman_phone,level,remark from student  as a ${ew.customSqlSegment}")
    public abstract IPage<Student> findAll(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper);

    @Select("select count(distinct course_id) as apply_course_count from order_detail ${ew.customSqlSegment}")
    public abstract Integer getApplyCourseCount(@Param(Constants.WRAPPER) Wrapper wrapper);


    /**
     * 花名册
     * @param page
     * @param wrapper
     * @return
     */
    @Select("select id,CONCAT(name,remark) as name,school_id,head_img,sex,grade_id,class_name,school_name,address,is_guanzhu,is_gather,status,linkman,linkman_phone,level,remark from student  as a ${ew.customSqlSegment}")
    @Results({
            @Result(column = "id",property = "id"),
            @Result(
                    property = "signUpSchoolList",
                    column = "id",
                    many = @Many(select = "com.xmy.cultivate.mapper.OrderDetailMapper.getSignUpSchoolList")
            )
    })
    public IPage<Student> rosterList(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper);

    /**
     * 欠费
     * @param wrapper
     * @return
     */
    @Select("select sum(IFNULL(fnGetOwnForStuId(a.id),0)) as ownPrice from student  as a ${ew.customSqlSegment}")
    public BigDecimal arrearage(@Param(Constants.WRAPPER) Wrapper wrapper);




    @Select("select a.*,(select count(id) from order_detail as b where b.student_id =a.id and b.status<>2 and residue_course_count>0) as apply_course_count from student  as a ${ew.customSqlSegment}")
    public abstract IPage<Student> signUpStu(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper);


    //@Select("select c.*,d.* from (select a.student_id,a.reading_status,sum(b.price) as price,sum(b.solid_price) as solid_price,sum(b.residue_price) as residue_price,sum(b.use_price) as use_price,sum(b.all_course_count) as all_course_count,sum(b.use_course_count) as use_course_count,sum(b.residue_course_count) as residue_course_count  from student_grade a LEFT JOIN order_detail b on a.order_detail_id = b.id where a.grade_id=#{gradeId}  ORDER BY a.id desc limit 0,1000) c LEFT JOIN student d on c.student_id = d.id GROUP BY c.student_id")
    @Select("select a.* from student as a ${ew.customSqlSegment}")
    public abstract IPage<Student> getStudentForGradeId(IPage<?> page,@Param(Constants.WRAPPER) Wrapper wrapper);


    /**
     * 班级里弹窗的待分班学员
     * @param page
     * @param wrapper
     * @param schoolId
     * @param courseId
     * @return
     */
    @Select("select * from (select a.student_id,b.name as student_name,a.handle_date from order_detail as a LEFT JOIN student as b on a.student_id=b.id where  a.status <>2 and a.residue_course_count>0 and a.id not in(select order_detail_id from student_grade as c where a.id = c.order_detail_id and c.deleted=0 and c.status=1 and c.reading_status<>6) and a.deleted = 0 and a.school_id = #{schoolId} and a.course_id = #{courseId}) as t ${ew.customSqlSegment}")
    public abstract IPage<SelectStudent> getStudentForShift(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper, @Param("schoolId") Long schoolId, @Param("courseId") Long courseId);

    @Select("SELECT count(student_id), s.name,s.linkman_phone\n" +
            " FROM student_grade sg\n" +
            " INNER JOIN student s ON s.id = sg.student_id\n" +
            "\tINNER JOIN grade g ON sg.grade_id = g.id")
    public abstract List<Student> selectMyStu(@Param(Constants.WRAPPER)Wrapper wrapper, @Param("teacherId")String teacherId);

    @Select("SELECT count(student_id) as courseNum, s.name,s.linkman_phone\n" +
            " FROM student_grade sg\n" +
            " INNER JOIN student s ON s.id = sg.student_id\n" +
            "\tINNER JOIN grade g ON sg.grade_id = g.id" +
            " ${ew.customSqlSegment}")
    public abstract IPage<Student> selectOther(IPage<Student> page,@Param(Constants.WRAPPER)Wrapper wrapper);

    @Select("select CONCAT( `name`, remark ) AS `name` from student where id=#{id}")
    public abstract Student getNameAndRemarkById(@Param("id") Long id);

    @Select("select `name` from student where id=#{id}")
    public abstract String getNameById(@Param("id") Long id);
}
